package com.iris.live.services.data.repositories.impl;

import com.google.common.collect.Maps;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.repositories.顾客记录汇总SQLRepository;
import com.iris.live.services.models.PageData;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lusong on 2016/11/8.
 */
@Repository
@Transactional//(readOnly = true)
public class 顾客记录汇总SQLRepositoryImpl extends  BaseRepository implements 顾客记录汇总SQLRepository {

    @Override
    public PageData scFailNewAssigned(String permit,int pageIndex,int pageSize,String search){
        StringBuffer sb = new StringBuffer();
        sb.append(" select ");
        sb.append(" a.电话号码 custtel, ");
        sb.append(" a.顾客姓名 custname, ");
        sb.append(" a.顾客编号 custid, ");
        sb.append(" a.所属销售顾问 sccode, ");
        sb.append(" b.人员姓名 scname, ");
        sb.append(" c.车型详细_CN car, ");
        sb.append(" a.原顾客级别 level, ");
        sb.append(" a.上次进店日期 inroomtime, ") ;
        sb.append(" a.CRM战败确认 status ");
        sb.append(" from `顾客记录汇总` a  ");
        sb.append(" left join 用户 b ");
        sb.append(" on a.所属销售顾问 = b.用户名 and a.许可 = b.许可 ");
        sb.append(" left join lsh车型 c ");
        sb.append(" on a.意向车型A = c.车型编号 ");
        sb.append(" where a.许可 =:permit ");
        sb.append(" and a.`顾客状态` = '本月' and a.CRM战败确认 = '1' ");
        if(search != null && !"".equals(search)){
            sb.append(" AND (a.电话号码 like :search or a.`所属销售顾问` like :search or 顾客姓名 LIKE :search) ");
        }
        sb.append(" order by a.上次进店日期 desc ");
        Map<String,String> params = Maps.newHashMap();
        params.put("permit",permit);
        if(search != null && !"".equals(search)){
            params.put("search","%" + search + "%");
        }

        return this.getPage(sb.toString(),params,pageSize,pageIndex);
    }

    @Override
    public List<?> getAssignedSC(String permit,String currentSC,String sccode) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT " +
                " 	所属销售顾问 sccode, " +
                " 	n.人员姓名 scname, " +
                " 	IF(e.值 IS NULL, 1, e.值 - d.cou) num " +
                " FROM " +
                " 	( " +
                " 		SELECT " +
                " 			许可,`所属销售顾问`, " +
                " 			COUNT(1) cou " +
                " 		FROM " +
                " 			( " +
                " 				SELECT " +
                " 					a.`所属销售顾问`, " +
                " 					a.许可 " +
                " 				FROM `顾客记录汇总` a " +
                " 				WHERE a.`是否交车` = '否' " +
                " 				AND a.`顾客状态` <> '战败' " +
                " 				AND a.`许可` = :permit " +
                " 				UNION ALL " +
                " 				SELECT " +
                " 					b.`所属销售顾问`, " +
                " 					b.许可 " +
                " 				FROM `lsh导入名单` b " +
                " 				WHERE b.`是否线索` = '是' " +
                " 				AND b.`是否有效线索` = '是' " +
                " 				AND b.`线索创建日期` >= date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) -1 day),interval 2 month) " +
                " 				AND b.`许可` = :permit " +
                " 			) c " +
                " 		WHERE c.所属销售顾问 <> '' " +
                " 		AND c.所属销售顾问 IS NOT NULL " +
                " 		GROUP BY " +
                " 			c.所属销售顾问 " +
                " 	) d " +
                " LEFT JOIN `用户` n ON d.所属销售顾问 = n.用户名 and n.许可 = d.许可 " +
                " LEFT JOIN ( " +
                " 	SELECT * FROM `销售项目设置` e " +
                " 	WHERE e.许可 = :permit " +
                " 	AND 设置项目 = '线索总数上限' " +
                " ) e ON d.许可 = e.许可 " +
                " WHERE n.删除状态 <> '已删除' " +
                " and 所属销售顾问 <> :currentSC " +
                " and IF(e.值 IS NULL, 1, e.值 - d.cou) > 0 ");
        if (sccode != null && !"".equals(sccode)){
            sb.append("     and 所属销售顾问 =:sccode ");
        }
        Map params = Maps.newHashMap();
        params.put("permit",permit);
        params.put("currentSC",currentSC);
        if(sccode != null && !"".equals(sccode)){
            params.put("sccode",sccode);
        }
        return this.getList(sb.toString(),params);
    }

    public List<?> cusInfoWhenCrmSurvey(String stage, String custId, String id) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT ");
        if (!StringUtils.isNullOrBlank(id)) {
            sb.append("'" +id + "'" + " as id, ");
        } else {
            sb.append(" '' as id, ");
        }
        if ("01".equals(stage)) {
            sb.append(" 	'首次离店调研' stage, '' neworold, ");
        } else if ("02".equals(stage)) {
            sb.append(" 	'休眠调研' stage, '' neworold, ");
        } else if ("03".equals(stage)) {
            sb.append(" 	'战败调研' stage, '' neworold, ");
        } else if ("04".equals(stage)) {
            sb.append(" 	'NPS调研' stage, 'Y' neworold, ");
        }
        sb.append(" 	a.`顾客编号` custId, ");
        sb.append(" 	a.`顾客姓名` cusName, ");
        sb.append(" 	CASE WHEN a.`顾客性别` = '男' THEN '先生' ");
        sb.append("     WHEN a.`顾客性别` = '女' THEN '女士' ");
        sb.append("     ELSE '' END cusSex, ");
        sb.append(" 	a.`电话号码` cusPhone, ");
        sb.append(" 	a.`顾客渠道` cusChannel, ");
        sb.append(" 	'' cusOriginType, ");
        sb.append(" 	a.`顾客来源` cusOrigin, ");
        sb.append(" 	a.`所属销售顾问` scCode, ");
        sb.append(" 	b.`人员姓名` scName, ");
        sb.append(" 	c.`车型` intentCarStyle, ");
        sb.append(" 	a.`新顾客级别` intentLevel, ");
        sb.append(" 	a.`留档日期` firstToShopDate, ");
        sb.append(" 	a.`上次进店日期` lastDateAboutShop, ");
        sb.append(" 	a.`新车销售状态` newCarSalesState ");
        sb.append(" FROM ");
        sb.append(" 	`顾客记录汇总` a ");
        sb.append(" LEFT JOIN `用户` b ON a.`所属销售顾问` = b.`用户名` AND a.许可 = b.`许可` ");
        sb.append(" LEFT JOIN `lsh车型` c ON a.`意向车型A` = c.`车型编号` ");
        sb.append(" WHERE ");
        sb.append(" 	a.`顾客编号` =:custId ");
        Map params = new HashMap();
        params.put("custId", custId);
        return this.getList(sb.toString(), params);
    }

    public List findLshLeandingInByPhoneAndPermit(String phone, String permit) {
        String sql = " SELECT * FROM `lsh导入名单` a WHERE a.`许可` = :permit AND a.电话号码 = :phone AND a.`是否线索` = '是' AND a.`是否有效线索` = '是' " +
                " AND a.`线索创建日期` >= date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) -1 day),interval 2 month) ";
        Map param = new HashMap();
        param.put("permit", permit);
        param.put("phone", phone);
        return this.getList(sql, param);
    }
}
